<?php

 
 Zend_Loader::loadClass("BaseDBRows");
 Zend_Loader::loadClass("Inventory");
 class Inventories extends BaseDBRows
 {
 	protected function setUp()
 	{
 		$this->_tableName = Inventory::_tablename;
 	}	
	
	public function getOrgInventoryByOrgId($orgId,$tank="",$moid="",$organismGroup="",$otherFilter="",$searchName="",$sizecode="", $searchAlpha="", $reload="") 
	{
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("Inventory");
		Zend_Loader::loadClass("Tank");
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("OrganismAttribute");
		Zend_Loader::loadClass("Size");
		Zend_Loader::loadClass("Organism");
		Zend_Loader::loadClass("OrganismGroup");
		Zend_Loader::loadClass("InventoryDAA");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("s.size_code,t.name tank_name, o.moid, o.name common_name, s.size, sum(i.quantity_on_hand) total_qty_on_hand ");
		$query->addSelect("sum(i.quantity_on_shipment) total_qty_on_shipment, i.currency_mark, sum(i.stock_amount) total_stock_amount ");
		$query->addSelect("o.organism_group_id, og.organism_group, oo.reorder_level, o.mac_certified, replace(o.scientific_name,' ','-') as image  , o.scientific_name");
		//$query->addSelect("ifnull((select 1 from " . InventoryDAA::_tablename . " where inventory_id = i.inventory_id),0) has_daa");
		$query->addSelect("sum(id.quantity_daa) quantity_daa, max(id.date_mortality) date_mortality, i.inventory_id");
		$query->addFrom($this->_tableName . " as i ");
		$query->addJoin(" left join " . Tank::_tablename . " t on i.tank_id = t.tank_id and i.org_id = t.org_id ");
		$query->addJoin(" left join " . InventoryDAA::_tablename . " id on i.inventory_id = id.inventory_id ");
		$query->addJoin("," . OrganizationOrganism::_tablename . " oo ");
		$query->addJoin("," . OrganismAttribute::_tablename . " oa ");
		$query->addJoin(" left join " . Size::_tablename . " s on oa.size_code = s.size_code ");
		$query->addJoin("," . Organism::_tablename . " o ");
		$query->addJoin(" left join " . OrganismGroup::_tablename . " og on o.organism_group_id = og.organism_group_id ");
		$query->addWhere(" i.org_id = oo.org_id " );
		$query->addWhere(" i.org_organism_id = oo.org_organism_id ");
		$query->addWhere(" i.organism_attribute_id = oa.organism_attribute_id ");
		$query->addWhere(" oo.organism_id = o.organism_id ");
		$query->addWhere(" i.org_id = " . $orgId);
		$query->addGroupBy(" t.name, o.moid, o.name, s.size, i.currency_mark, og.organism_group, o.organism_group_id ");
		$query->addGroupBy("  oo.reorder_level, o.mac_certified, i.inventory_id");
		
		// tank
		if(strlen($tank) > 0) {
			$query->addWhere("t.tank_id = " . $tank);
		}
		// moid
		if(!is_array($moid)) {
			if(strlen($moid) > 0) {
				$query->addWhere("o.moid = '" . $moid . "'");	
			}
		} else {
			$query->addWhere("o.moid  in (". implode(",",$moid) .")");	
			
		}
		// organism group
		if(strlen($organismGroup) >0) {
			$query->addWhere("o.organism_group_id =" . $organismGroup);
		}
		
		// search name
		if(strlen($searchName) > 0) {
			$query->addWhere("o.name regexp '" . $searchName . "'");	
		}
		
		// reload
		
		if(strlen($reload) <= 0) {
			$searchAlpha = '#';	
		}
		
		// alpha
		if(strlen($searchAlpha) > 0) {
			if ($searchAlpha == '#') {
				$query->addWhere("(ascii(lower(left(o.name,1))) < 97 or ascii(lower(left(o.name,1))) > 122)");
			} else {
				$query->addWhere("lower(left(o.name,1)) like '". $searchAlpha . "%'");
			}
		}
		
		if(strlen($sizecode) > 0)
			$query->addWhere(" oa.size_code = '". $sizecode . "'");
	
		switch($otherFilter) {
			case 1:
				// Below Reorder Level
				$query->addHaving(" total_qty_on_hand < oo.reorder_level ");
				break;
			case 2:
				/*$query->addJoin(", (select distinct a.org_organism_id from " . OrganizationOrganism::_tablename . "a left join (select org_organism_id, sum(quantity_on_hand) qty_on_hand from " . $this->_tableName . " b group by org_organism_id) b on a.org_organism_id = b.org_organism_id where org_id = " . $orgId . " and ifnull(a.ordering_status,'Active') = 'Active' and b.qty_on_hand is null) x ");*/
				break;
			case 3:
				$query->addHaving(" quantity_daa > 0 ");
				break;
		}
		
		
		
		$db = DBCon::instance();
		//echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;

	}

	public function getOrgInvSelectionByOrgId($orgId) {
	
		Zend_Loader::loadClass("Tank");
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("Organism");
		Zend_Loader::loadClass("OrganismAttribute");
		Zend_Loader::loadClass("Size");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("i.inventory_id, i.tank_id, i.org_organism_id, i.organism_attribute_id");
		$query->addSelect("concat('Tank \'',t.name,'\' contains \'',o.name,' (',s.size,')\' of \'',i.quantity_on_hand,'\' pcs') source");
		$query->addFrom($this->_tableName . " i ");
		$query->addJoin("," . Tank::_tablename . " t ");
		$query->addJoin("," . OrganizationOrganism::_tablename . " oo ");
		$query->addJoin("," . Organism::_tablename . " o ");
		$query->addJoin("," . OrganismAttribute::_tablename . " oa ");
		$query->addJoin("," . Size::_tablename . " s ");
		$query->addWhere("i.tank_id = t.tank_id");
		$query->addWhere("i.org_id = t.org_id");
		$query->addWhere("i.org_organism_id = oo.org_organism_id");
		$query->addWhere("i.org_id = oo.org_id");
		$query->addWhere("oo.organism_id = o.organism_id");
		$query->addWhere("i.organism_attribute_id = oa.organism_attribute_id");
		$query->addWhere("oa.size_code = s.size_code");
		$query->addWhere("i.org_id = " . $orgId);
		$query->addOrderBy("t.name, o.name, i.quantity_on_hand");
		
		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function exportFile($orgid){
	
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("Organism");
		Zend_Loader::loadClass("OrganismAttribute");
		Zend_Loader::loadClass("Size");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("Tank");
		
		$query = new QueryCreator();
		$query->addSelect("b.moid,concat(b.name,'<br>',b.scientific_name),c.size_code,d.size,t.tank_id,t.name as tank_name");
		$query->addFrom(OrganizationOrganism::_tablename . " a ");
		$query->addJoin(" LEFT JOIN ". Organism::_tablename . " b on a.organism_id = b.organism_id ");
		$query->addJoin(" LEFT JOIN ". OrganismAttribute::_tablename . " c on c.organism_id=b.organism_id ");
		$query->addJoin(" LEFT JOIN ". Size::_tablename . " d on d.size_code = c.size_code");
		$query->addJoin(" LEFT JOIN ". Tank::_tablename ." t on t.org_id = a.org_id ");
		$query->addWhere(" a.org_id = ". $orgid );
		
		$db = DBCon::instance();
		print $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		//print count($result);die();
		return $result;
	}
	
	public function export2File($orgid){
	
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("Organism");
		Zend_Loader::loadClass("OrganismAttribute");
		Zend_Loader::loadClass("Size");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("Tank");
		
		$query = new QueryCreator();
		$query->addSelect("b.moid,concat(b.name,'<br>',b.scientific_name),c.size_code,d.size,t.tank_id,t.name as tank_name");
		$query->addFrom(OrganizationOrganism::_tablename . " a ");
		$query->addJoin(" LEFT JOIN ". Organism::_tablename . " b on a.organism_id = b.organism_id ");
		$query->addJoin(" LEFT JOIN ". OrganismAttribute::_tablename . " c on c.organism_id=b.organism_id ");
		$query->addJoin(" LEFT JOIN ". Size::_tablename . " d on d.size_code = c.size_code");
		$query->addJoin(" LEFT JOIN ". Tank::_tablename ." t on t.org_id = a.org_id ");
		$query->addWhere(" a.org_id = ". $orgid );
		
		$db = DBCon::instance();
		print $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		//print count($result);die();
		return $result;
	}	
	
 }
?>